package taobao

import org.apache.spark.SparkContext
import org.apache.spark.sql.SparkSession

/**
  * @ Auther: o2o-rd-0008
  * @ Date:   2020/11/17 16:36
  * @ Param:  ${PARAM}
  * @ Description: 
  */
object RegDeal {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder()
      .config("spark.debug.maxToStringFields", "2000")
      .config("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
        .master("local[*]")
      .getOrCreate()


    val sc: SparkContext = spark.sparkContext
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
    sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")
    sc.setLogLevel("WARN")

    /*val sourceDF: DataFrame = spark.read.orc("s3a://o2o-dataproces-group/zsc/2020/10/rongyigou/good_final/")
                                .drop("firstCategoryId").drop("secondCategoryId").drop("thirdCategoryId").drop("fourthCategoryId").cache()

    val cateDF: DataFrame = spark.read.option("header",true).csv("s3a://o2o-dataproces-group/zsc/2020/10/rongyigou/rongyigou_cate/").dropDuplicates()
      .withColumnRenamed("platform_category_id","subCategoryId")
      .withColumnRenamed("o2o_firstcategoryid","firstCategoryId")
      .withColumnRenamed("o2o_secondcategoryid","secondCategoryId")
      .withColumnRenamed("o2o_threecategoryid","thirdCategoryId")
      .withColumnRenamed("o2o_fourcategoryid","fourthCategoryId").cache()

    val result: DataFrame = sourceDF.join(cateDF,Seq("subCategoryId")).cache()

    result.write.orc("s3a://o2o-dataproces-group/zsc/2020/10/rongyigou/big_cate_new/")*/

    spark.read.orc("s3a://o2o-dataproces-group/zsc/2020/10/rongyigou/big_cate_new/").cache().registerTempTable("tab")
    spark.read.orc("s3a://o2o-dataproces-group/zsc/2020/10/rongyigou/good_final/").cache().registerTempTable("stab")
    spark.read.option("header",true).csv("s3a://o2o-dataproces-group/zsc/jumei_new_cate/all_cate/").cache().registerTempTable("catetab")

    spark.sql(
      """
        |select
        |fourthCategoryId,
        |sum(salesAmount),
        |sum(sellCount)
        |from
        |stab
        |where subCategoryId='43025002'
        |group by fourthCategoryId
      """.stripMargin).show(false)

    spark.sql(
      """
        |select
        |fourthCategoryId,
        |sum(salesAmount),
        |sum(sellCount)
        |from
        |stab
        |where subCategoryId='47005001'
        |group by fourthCategoryId
      """.stripMargin).show(false)



    /*spark.sql(
      """
        |select
        |t4.subCategoryName,
        |t3.*,
        |t5.subCategoryName as fourthnameold
        |from
        |(select
        |t1.*,
        |"" as tmp,
        |t2.*,
        |t1.salesnew-t2.salesold as sales_jian,
        |t1.sellnew-t2.sellold as sell_jian
        |from
        |(select
        |subCategoryId as subCategoryIdnew,
        |--categoryName,
        |firstCategoryId as firstCategoryIdnew,
        |secondCategoryId as secondCategoryIdnew,
        |thirdCategoryId as thirdCategoryIdnew,
        |fourthCategoryId as fourthCategoryIdnew,
        |sum(salesAmount) salesnew,
        |sum(sellCount) sellnew
        |from
        |stab
        |where thirdcategoryId in('100200402','100190105','100190107','100190203','100190601','100190108','100190106',
        |'100220201','100190801','100190204','100190202','100190101','100190201','100200202',
        |'100190102','100200401','100251002','100190106','100140103','100210203','100160703',
        |'100190205','100150103','100190603','100190605','100200201') or fourthCategoryId in ('10021020701','10019010301','10019010302')
        |or secondCategoryId in ('1001903','1001904')
        |group by subCategoryId,firstCategoryId,secondCategoryId,thirdCategoryId,fourthCategoryId) t1
        |left join
        |(select
        |subCategoryId as subCategoryIdold,
        |--categoryName,
        |firstCategoryId as firstCategoryIdold,
        |secondCategoryId as secondCategoryIdold,
        |thirdCategoryId as thirdCategoryIdold,
        |fourthCategoryId as fourthCategoryIdold,
        |sum(salesAmount) salesold,
        |sum(sellCount) sellold
        |from
        |tab
        |--where thirdcategoryId in('100200402','100190105','100190107','100190203','100190601','100190108','100190106',
        |--'100220201','100190801','100190204','100190202','100190101','100190201','100200202',
        |--'100190102','100200401','100251002','100190106','100140103','100210203','100160703',
        |--'100190205','100150103','100190603','100190605','100200201') or fourthCategoryId in ('10021020701','10019010301','10019010302')
        |--or secondCategoryId in ('1001903','1001904')
        |group by subCategoryId,firstCategoryId,secondCategoryId,thirdCategoryId,fourthCategoryId) t2
        |on t1.subCategoryIdnew=t2.subCategoryIdold
        |) t3
        |left join
        |(
        |select distinct subCategoryId,subCategoryName from catetab
        |) t4
        |on t3.subCategoryIdnew=t4.subCategoryId
        |left join
        |(
        |select distinct subCategoryId,subCategoryName from catetab
        |) t5
        |on t3.subCategoryIdold=t5.subCategoryId
      """.stripMargin).repartition(1).write.option("header",true).csv("s3a://o2o-dataproces-group/zsc/rongyigou_new_cate/1/")*/


  }
}
